<?php
/**
 * synchronize db with metabase
 */
 
class syncro{	
	private $aDBPar = null;		//database param
	private $aMBPar = null;		//metabase paran
	private $objDB = null; 		// database
	private $objMB = null;			// metabase
	private $aObjMB = array(); 		//MB items 
	private $aObjDB = array(); 		//DB items
	private $aSQLSync = array(); 	// SQL statements
	private $bSyncWithDrop = false;
	private $reqtype = 'cnp'; // cmp = compare - syn = comparew and synchronize
	public $error = "";
	public $errno = 0;
	public $aEntAltered = array();
	public $aEntCreated = array();
	public $aEntDeleted = array();
	
	public function __construct($aDBPar, $aMBPar, $aOpzioni = null) {
		$this->reqtype = 'cmp';
		if (is_array($aOpzioni)) {
			if (isset($aOpzioni['reqtype'])) {$this->reqtype = $aOpzioni['reqtype'];}
		}
		$this->bSyncWithDrop = false;
		if (is_array($aOpzioni) and isset($aOpzioni['withDrop'])) {
			$this->bSyncWithDrop = $aOpzioni['withDrop'];
		} elseif (defined('DB_SYNC_WITH_DROP') and DB_SYNC_WITH_DROP === true) {
			$this->bSyncWithDrop = true;
		}
		$this->aDBPar = $aDBPar;
		$this->aMBPar = $aMBPar;
		$this->objDB = new SDAO($aDBPar);
		$this->errno = $this->objDB->errno;
		$this->error = $this->objDB->error;
		
		if($this->errno != 0){
			$nomeDB = $this->aDBPar["dbName"];
			$this->error .= "<br>"._t("No database")." '$nomeDB' "._t(". Create it before synchro.");
			return ;
		}
		$this->objMB = new SDAO($aMBPar);
		$this->errno = $this->objMB->errno;
		$this->error = $this->objMB->error;
		if($this->errno != 0) return ;
		$this->loadMBData();
		if($this->errno != 0) return ;
		$this->loadDBSchema();
		if($this->errno != 0) return ;
		$this->compare();
		if($this->errno != 0) return ;
		if($this->reqtype == 'syn') $this->sync();
	}
	
	public function __destruct() {
      $this->objDB = null; 
      $this->objMB = null;
   }

   private function backupDB(){
   	//back-up copy -- TO DO ?
   }
   
   private function loadMBData(){
   	//load entities
   	$sSQL = "SELECT * FROM entity ORDER BY entity";
   	if($this->objMB->query($sSQL, "ent", array("contOnErr"=>true))){
   		while($row = $this->objMB->fetch("ent")){
   			//load entity attribute
   			$sSQL = "SELECT a.*, d.type, d.length AS d_length, d.decimals AS d_decimals " .
   					" FROM attribute a 
   					INNER JOIN domain d ON a.domain=d.domain 
   					WHERE entity='".str_replace("'", "''", $row["entity"])."' " .
   							" ORDER BY sequence";
   			$this->objMB->query($sSQL, "attr");
   			while($rowAttr = $this->objMB->fetch("attr")){
   				$sSQL = "SELECT count(*) as isChildAttr FROM relationship_key rk" .
   							" INNER JOIN relationship r ON rk.parent_entity = r.parent_entity " .
   							"    AND rk.child_entity = r.child_entity " .
   							"    AND rk.number = r.number " .
   							" WHERE rk.child_entity='".$row["entity"]."' " .
   							"   AND rk.child_attribute='".$rowAttr["attribute"]."' AND r.integrity_check = 'Y'";
   				$this->objMB->query($sSQL, "ch_attr");
					if($rowChAttr = $this->objMB->fetch("ch_attr")){
						$rowAttr["isChildAttr"] = $rowChAttr["isChildAttr"];
					}
					$this->aObjMB[$row["entity"]]["attr"][$rowAttr["attribute"]] = $rowAttr;
   			}
   			$this->aObjMB[$row["entity"]]["ent"] = $row;
   		}	
   	}else{
   		$this->errno = $this->objMB->errno;
   		$this->error = $this->objMB->error;
   		return false;
   	}   	
   }
   
   private function compare(){
   	/**
   	attribute type:
   	S = string				V = string variable
		D = date					T = date-time
		N = number				B = boolean (0,1)
		I = integer				X = text
		L = long-text
		*/
		foreach($this->aObjMB as $entMB=>$aFeature){
   		/**dbg_(print_r($this->aObjDB ,1));*/
   		/**dbg_(print_r($this->aObjMB ,1));*/
   		$aAttr = $aFeature["attr"];
   		$aEntFeature = $aFeature["ent"];
   		if(isset($this->aObjDB[$entMB])){
			//existing entity
   			$primKeyFields = "";
	   		$sepPK = "";
	   		$oldPrimKeyFields = "";
	   		$sepOldPK = "";
	   		foreach($aAttr as $attrMb=>$aDefAttr){
   				if(isset($this->aObjDB[$entMB][$attrMb])){
   					//existing attrib. -> change
   					$sSQLAlter = $this->alterAttr($entMB, $attrMb);
				   	if($sSQLAlter != "" && $this->errno == 0){
				   		$this->aSQLSync[] = array($sSQLAlter, _t("Attributo: ").$attrMb." "._t("dell'entity").": $entMB "._t("modificato"));
				   		$this->aEntAltered[$entMB]['a'][$attrMb]['old'] = $this->getDefDB($this->aObjDB[$entMB][$attrMb]);
				   		$this->aEntAltered[$entMB]['a'][$attrMb]['new'] = $this->getDefMB($aDefAttr);
   					}
						$sSQLInd = $this->alterIndex($entMB, $attrMb);
				   	if($sSQLInd != "" && $this->errno == 0){
				   		$this->aSQLSync[] = array($sSQLInd, _t("Attributo: ").$attrMb." "._t("dell'entity").": $entMB "._t("indice modificato"));
				   		if ($sSQLInd[0]=='C')$this->aEntAltered[$entMB]['i'][$attrMb]['new'] = $attrMb;
				   		if ($sSQLInd[0]!='C')$this->aEntAltered[$entMB]['i'][$attrMb]['old'] = $attrMb;
   					}
   					if($aDefAttr["primary_key"] != "N"){
   						$primKeyFields .= $sepPK.$attrMb;
   						$sepPK = ", ";
   					}
   					if($this->aObjDB[$entMB][$attrMb]["Key"] == "PRI"){
   						$oldPrimKeyFields .= $sepOldPK.$attrMb;
   						$sepOldPK = ", ";
   					}
   				}else{
   					//new attribute -> create
			   		$this->aEntAltered[$entMB]['a'][$attrMb]['new'] = $this->getDefMB($aDefAttr);
   					$sSQLAlter = "ALTER TABLE $entMB ADD COLUMN $attrMb ";
   					$sSQLAlter .= $this->getSQLAttr($aDefAttr);
	   				$sSQLAlter .= "\n";
	   				$this->aSQLSync[] = array($sSQLAlter, _t("Attribute")." $attrMb "._t("of entity").":$entMB "._t("created"));
   					if($aDefAttr["indexed"] == "Y" && $aDefAttr["primary_key"] == "N"){
	   					$sSQLIndex = "CREATE INDEX $attrMb ON $entMB ($attrMb)";
	   					$this->aSQLSync[] = array($sSQLIndex, _t("Index created"));
	   					$this->aEntAltered[$entMB]['i'][$attrMb]['new'] = $attrMb;
	   				}
	   				if($aDefAttr["indexed"] == "N" && $aDefAttr["primary_key"] == "N" && $aDefAttr["isChildAttr"]){
	   					$sSQLIndex = "CREATE INDEX $attrMb ON $entMB ($attrMb)";
	   					$this->aSQLSync[] = array($sSQLIndex, _t("Index created"));
	   					$this->aEntAltered[$entMB]['i'][$attrMb]['new'] = $attrMb;
	   				}		   				   				
   					if($aDefAttr["primary_key"] != "N"){
   						$primKeyFields .= $sepPK.$attrMb;
   						$sepPK = ", ";
   					} 	
   					if(isset($this->aObjDB[$entMB][$attrMb]["Key"]) && $this->aObjDB[$entMB][$attrMb]["Key"] == "PRI"){
   						$oldPrimKeyFields .= $sepOldPK.$attrMb;
   						$sepOldPK = ", ";
   					}   				
   				}
   			}
   			if($primKeyFields != "" && $this->errno == 0 && $primKeyFields != $oldPrimKeyFields){
		   		$sSQL = "ALTER TABLE $entMB DROP PRIMARY KEY";
		   		$this->aSQLSync[] = array($sSQL, _t("Entity").": $entMB "._t("primary key")." [$primKeyFields] "._t("deleted"));
		   		$sSQL = "ALTER TABLE $entMB ADD CONSTRAINT pk_$entMB PRIMARY KEY ($primKeyFields) ";
		   		$this->aSQLSync[] = array($sSQL, _t("Entity").": $entMB "._t("primary key")." [$primKeyFields] "._t("created"));
	   			$this->aEntAltered[$entMB]['i']['PRIMARY KEY']['old'] = $oldPrimKeyFields;
	   			$this->aEntAltered[$entMB]['i']['PRIMARY KEY']['new'] = $primKeyFields;
				}		
   			foreach($this->aObjDB[$entMB] as $attrDB => $aFeature) {
   				if (!isset($aAttr[$attrDB])) {
	   				$this->aEntAltered[$entMB]['a'][$attrDB]['old']=$this->getDefDB($aFeature);
	   				if ($this->bSyncWithDrop) {
	   					$sSQLAlter = "ALTER TABLE $entMB DROP COLUMN $attrDB ";
		   				$this->aSQLSync[] = array($sSQLAlter, _t("Attribute")." $attrDB "._t("of entity").
								":$entMB "._t("deleted (DROP)"));	   					
	   				}
   				}
	   		}
   		}else{
   			//not existing table
   			$engine = "MyISAM";
   			$sXMLPhyParam = $aEntFeature["physical_param"];
   			if($sXMLPhyParam != ""){
	   			//get physical param
	   			$doc = new DOMDocument();
					$doc->loadXML($sXMLPhyParam);
					$items = $doc->getElementsByTagName("engine");
					for ($i = 0; $i < $items->length; $i++) {
					    $engine = $items->item($i)->nodeValue;
					}
   			}
   			if(count($aAttr) > 0){
	   			$sSQLCreate = "CREATE TABLE $entMB (\n";
	   			$primKeyFields = "";
	   			$sepPK = "";
	   			$sepAttr = "";
	   			$aSQLIndex = array();
	   			foreach($aAttr as $attrMb=>$aDefAttr){
	   				$this->aEntCreated[$entMB]['a'][$attrMb] = $this->getDefMB($aDefAttr);
	   				$sSQLCreate .= $sepAttr.$attrMb;
	   				$sSQLCreate .= $this->getSQLAttr($aDefAttr);
	   				$sSQLCreate .= "\n";  
	   				if($aDefAttr["primary_key"] != "N"){
	   					$primKeyFields .= $sepPK.$attrMb;
	   					$sepPK = ", ";
	   				} 
	   				if($aDefAttr["indexed"] == "Y" && $aDefAttr["primary_key"] == "N"){
	   					$aSQLIndex["$entMB ($attrMb)"] = "CREATE INDEX $attrMb ON $entMB ($attrMb)";
	   					$this->aEntCreated[$entMB]['i'][$attrMb] = $attrMb;
	   				}
	   				if($aDefAttr["indexed"] == "N" && $aDefAttr["primary_key"] == "N" && $aDefAttr["isChildAttr"]){
	   					$aSQLIndex["$entMB ($attrMb)"] = "CREATE INDEX $attrMb ON $entMB ($attrMb)";
	   					$this->aEntCreated[$entMB]['i'][$attrMb] = $attrMb;
	   				}		   				   				
	   				$sepAttr = ", ";			
	   			} 
	   			if($primKeyFields != "") {
	   				$sSQLCreate .= $sepAttr." PRIMARY KEY ($primKeyFields)"; 	
	   				$this->aEntCreated[$entMB]['i']['PRIMARY KEY'] = $primKeyFields;
	   			}		
	   			$sSQLCreate .= ")\n ENGINE=$engine\n";
	   			$this->aSQLSync[] = array($sSQLCreate, _t("Entity ").$entMB.": "._t("created"));
	   			foreach($aSQLIndex as $index=>$sSQL){
	   				$this->aSQLSync[] = array($sSQL, _t("Index")." [$index] "._t("created"));
	   			}
	   		}
   		}
   	} 
   	foreach($this->aObjDB as $entDB=>$aFields){
   		if (!isset($this->aObjMB[$entDB])) {
   			foreach($aFields as $attrDB => $aFeature)
	   			$this->aEntDeleted[$entDB][$attrDB]=$this->getDefDB($aFeature);
   				if ($this->bSyncWithDrop) {
   					$sSQLAlter = "DROP TABLE $entDB  ";
	   				$this->aSQLSync[] = array($sSQLAlter,_t("Entity").
							":$entMB "._t("deleted (DROP)"));	   					
   				}
   		}
   	}
   }
   
   private function getDefDB($aFeature) {
   	$desType = $aFeature['Type'];
   	if ($aFeature['Extra']!='') $desType .= ' '.$aFeature['Extra'];
   	if ($aFeature['Default']!='') $desType .= ' def: '.$aFeature['Default'];
   	return $desType;
   }
   
   private function getDefMB($aDefAttr) {
   	$attrType = $aDefAttr["type"];
   	$length = ($aDefAttr["length"]==0)?$aDefAttr["d_length"]:$aDefAttr["length"];
   	$decimals = ($aDefAttr["decimals"]==0)?$aDefAttr["d_decimals"]:$aDefAttr["decimals"];  	
   	switch($attrType){
			case "string": //S = string
				$desType = "char(".$length.") def: '".addslashes($aDefAttr["default_value"])."'";
				break;
			case "variable-string": //V = string variable
				$desType = "varchar(".$length.") def: '".addslashes($aDefAttr["default_value"])."'";
				break;
			case "integer": // I = integer
				if ($aDefAttr["primary_key"] == 'X') {
					$desType = "int(11) AUTO_INCREMENT";
				} else {
					$desType = "int(11) def: ".intval($aDefAttr["default_value"]);
				}
				break;
			case "date": // D = date
					$desType = "date";
				if ($aDefAttr["default_value"] != '')
					$desType  .= " def: '".addslashes($aDefAttr["default_value"])."'";
				break;
			case "date-time": // T = date-time
				$desType = "datetime  def: '".addslashes($aDefAttr["default_value"])."'";
				break;
			case "number": // N = number
				$desType = "decimal(".$length.",".$decimals.") def: ".
				number_format($aDefAttr["default_value"],$decimals);
				break;
			case "boolean": // B = boolean (0,1)
				$desType = "char(1)  def: '".(($aDefAttr["default_value"]=='1')?'1':'0')."'";
				break;
			case "text": // T = text
				$desType = "text def: ''";
				break;
			case "long-text": // L = long-text
				$desType = "text def: ''";
				break;
			case "blob": // O = blob
				$desType = "blob";
				break;
			default:
				$desType = "varchar(80)  def: ''";
		}
		return $desType;
   }
   
   private function getSQLAttr($aDefAttr){
   	$sSQLAttr = "";
   	$attrType = $aDefAttr["type"];
   	$length = ($aDefAttr["length"]==0)?$aDefAttr["d_length"]:$aDefAttr["length"];
   	$decimals = ($aDefAttr["decimals"]==0)?$aDefAttr["d_decimals"]:$aDefAttr["decimals"];
   	
   	switch($attrType){
			case "string": //S = string
				$sSQLAttr .= " char(".$length.") NOT NULL DEFAULT '".addslashes($aDefAttr["default_value"])."'";
				break;
			case "variable-string": //V = string variable
				$sSQLAttr .= " varchar(".$length.") NOT NULL DEFAULT '".addslashes($aDefAttr["default_value"])."'";
				break;
			case "integer": // I = integer
				if ($aDefAttr["primary_key"] == 'X') {
					$sSQLAttr .= " int(11) AUTO_INCREMENT";
				} else {
					$sSQLAttr .= " int(11) NOT NULL DEFAULT ".intval($aDefAttr["default_value"])."";
				}
				break;
			case "date": // D = date
					$sSQLAttr .= " date NOT NULL ";
				if ($aDefAttr["default_value"] != '')
					$sSQLAttr .= " DEFAULT '".addslashes($aDefAttr["default_value"])."'";
				break;
			case "date-time": // T = date-time
				$sSQLAttr .= " datetime  NOT NULL ";
				if ($aDefAttr["default_value"] != '')
					$sSQLAttr .= " DEFAULT '".addslashes($aDefAttr["default_value"])."'";
				break;
			case "number": // N = number
				$sSQLAttr .= " decimal(".$length.", ".$decimals.") NOT NULL DEFAULT ".
				number_format($aDefAttr["default_value"],$decimals)."";
				break;
			case "boolean": // B = boolean (0,1)
				$sSQLAttr .= " char(1)  NOT NULL DEFAULT '".(($aDefAttr["default_value"]=='1')?'1':'0')."'";
				break;
			case "text": // T = text
				$sSQLAttr .= " text NOT NULL DEFAULT ''";
				break;
			case "long-text": // L = long-text
				$sSQLAttr .= " text NOT NULL DEFAULT ''";
				break;
			case "blob": // O = blob
				$sSQLAttr .= " blob";
				break;
			default:
				$sSQLAttr .= " varchar(80)  NOT NULL DEFAULT ''";
		}
		return $sSQLAttr;
   }
   
   private function sync(){
   	//do synchro
   	foreach($this->aSQLSync as $elem=>$aStmt){
   		if(!$this->objDB->query($aStmt[0], "sync", array("contOnErr"=>true))){
   			$this->errno = $this->objDB->errno;
   			$this->error .= "<br>".$this->objDB->error."<br>".$aStmt[0];	
   		}
   	}
   	$this->convLatin1toUtf8();
   }
   
    private function loadDBSchema(){
   	/** 
   	 * Carica la struttura di un db MYSQL
   	 */
   	//load table
   	$sSQL = "SHOW TABLES";
   	if($this->objDB->query($sSQL, "ent")){
 			while($rowEnt = $this->objDB->fetch("ent")){
		   	$entity = $rowEnt["Tables_in_".$this->aDBPar["dbName"]];
		   	//carico i campi della tabella
		   	$sSQL = " DESCRIBE $entity";
		 		if($this->objDB->query($sSQL, "attr")){
		 			while($rowAttr = $this->objDB->fetch("attr")){
		 				//individuo gli indici collegati all'attribute
		 				$sSQLIND = "SHOW INDEX FROM $entity WHERE Column_name='".$rowAttr["Field"]."' AND Key_name <> 'PRIMARY'";
		 				$this->objDB->query($sSQLIND, "index");
		 				$rowIndex = $this->objDB->fetch("index");
		 				if($rowIndex)
		 					$rowAttr["indexes"] = $rowIndex;
		 				$this->aObjDB[$entity][$rowAttr["Field"]] = $rowAttr;	 				
		 			} 			
		 		}else{
		 			$this->errno = $this->objMB->errno;
		   		$this->error = $this->objMB->error;
		   		return false;
		 		}
		 	} 			
 		}else{
 			$this->errno = $this->objMB->errno;
   		$this->error = $this->objMB->error;
   		return false;
 		}
   }
   
   private function alterAttr($entMB, $attrMb){
   	//build alter statement for modified attribute
   	$sSQL = "";
   	$aAttrMB = $this->aObjMB[$entMB]["attr"][$attrMb];
   	$aAttrDB = $this->aObjDB[$entMB][$attrMb];
   	//echo($aAttrMB["type"]." - ".trim($aAttrDB["Type"])." // ".$aAttrMB["length"]." - ".$aAttrDB["length"]);
   	if($this->isAttrChanged($aAttrMB, $aAttrDB))
   		$sSQL = "ALTER TABLE $entMB MODIFY COLUMN $attrMb ".$this->getSQLAttr($aAttrMB);
   	return $sSQL;
   }
   
   private function isAttrChanged($aAttrMB, $aAttrDB){
   	//test modified attribute
   	$res = false;
   	$tipoMB = "";
   	$length = ($aAttrMB["length"]==0)?$aAttrMB["d_length"]:$aAttrMB["length"];
   	$decimals = ($aAttrMB["decimals"]==0)?$aAttrMB["d_decimals"]:$aAttrMB["decimals"];
   	switch($aAttrMB["type"]){
   		case "string": //S = string
				$tipoMB = "char(".$length.")";
				break;
			case "variable-string": //V = string variable
				$tipoMB = "varchar(".$length.")";
				break;
			case "integer": // I = integer
				$tipoMB = "int(11)";
				break;
			case "date": // D = date
				$tipoMB = "date";
				break;
			case "date-time": // T = date-time
				$tipoMB = "datetime";
				break;
			case "number": // N = number
				$tipoMB = "decimal(".$length.",".$decimals.")";
				break;
			case "boolean": // B = boolean (0,1)
				$tipoMB = "char(1)";
				break;
			case "text": // T = text
				$tipoMB = "text";
				break;
			case "long-text": // L = long-text
				$tipoMB = "text";
				break;
			case "blob": // O = blob
				$tipoMB = "blob";
				break;
   		default :
				$tipoMB = "varchar(80)";
   	}
   	if($tipoMB != $aAttrDB["Type"])
   		$res = true;
   	if($aAttrMB["primary_key"] == 'X' && $aAttrDB["Extra"] != 'auto_increment'
   	|| $aAttrMB["primary_key"] != 'X' && $aAttrDB["Extra"] == 'auto_increment')
   		$res = true;   	
   	return $res;
   }
   
   private function alterIndex($entMB, $attrMb){
   	//build create index statement for new index.
   	//or drop index for old ones
   	//NB: child_attribute in relationship are always indexed.
   	$sSQL = "";
   	$aAttrMB = $this->aObjMB[$entMB]["attr"][$attrMb];
   	$aAttrDB = $this->aObjDB[$entMB][$attrMb];
   	if($aAttrMB["indexed"] == "Y" && !isset($aAttrDB["indexes"])){
   		$sSQL = "CREATE INDEX $attrMb ON $entMB ($attrMb)";
   	}else if($aAttrMB["indexed"] == "N" && isset($aAttrDB["indexes"])&& !$aAttrMB["isChildAttr"]){
   		$sSQL = "ALTER TABLE $entMB DROP INDEX $attrMb";
   	}else if($aAttrMB["primary_key"] == "N" && $aAttrMB["indexed"] == "N" && !isset($aAttrDB["indexes"]) && $aAttrMB["isChildAttr"]){
   		$sSQL = "CREATE INDEX $attrMb ON $entMB ($attrMb)";
   	}
   	
   	return $sSQL;   	
   }
   
   function convLatin1toUtf8() {	
		//$sql = "ALTER DATABASE  `".$this->aDBPar["dbName"]."` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";
		//$this->objDB->exec($sql);
   	//$this->error .= "<br>"."eseguito : $sql\n";	

		$sql = "SHOW TABLES";
		$this->objDB->query($sql,'ListaTabelle');
		
		while($row = $this->objDB->fetch('ListaTabelle')) {
			$aX = array_values($row);
			//$sql = "ALTER TABLE ".$aX[0]." CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci";
			//$this->objDB->exec($sql);
   		//$this->error .= "<br>"."eseguito : $sql\n";	
			$sql = "SHOW FULL COLUMNS FROM ".$aX[0];
			$this->objDB->query($sql,'listaCampi');
			while($row2 = $this->objDB->fetch('listaCampi')) {
				if (!empty($row2['Collation']) and $row2['Collation'] != 'utf8_general_ci') {
					$sql = "ALTER TABLE ".$aX[0]." CHANGE ".$row2['Field']." ".$row2['Field']." ".$row2['Type'].
					" CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '".addslashes($row2['Default'])."'";
					$this->objDB->exec($sql);
					$this->error .= "<br>".$aX[0].".".$row2['Field'].": collation '".$row2['Collation'].
						"' setta a 'utf8_general_ci' \n";
				}
			}
		}
   }		
}
   
?>